package com.hist.dao;

import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.beans.factory.annotation.Autowired;

import com.hist.entity.AddressCheck;
import com.hist.entity.AgeCheck;
import com.hist.entity.AllScore;
import com.hist.entity.Course;
import com.hist.entity.Percent;
import com.hist.entity.SpolCheck;
import com.hist.entity.Student;

@Mapper
public interface AdministratorDao {
	
        /*修改学生信息*/
        @Update("update studentmanagement.student set sname =#{sname},ssex=#{ssex},sage=#{sage},sdept=#{sdept},smajor=#{smajor},sgrade=#{sgrade},school_length=#{school_length},saddress=#{saddress},spol=#{spol},snation=#{snation},srp=#{srp},smove=#{smove} where sno =#{sno}")
        void updateStudent(Integer sno,String sname,String ssex,Integer sage,String sdept,String smajor,Integer sgrade,Integer school_length,String saddress,String spol,String snation,String srp,String smove);
        @Select("select * from studentmanagement.student where sno=#{sno}")
	    List<Student> getStudent(Integer sno);
        
        /*查看全体学生信息*/
        @Select("select * from studentmanagement.student")
        List<Student> allstudent();
	    /*按年龄统计人数*/
	    @Select("select sage as age,count(*) as count from studentmanagement.student group by sage")
	    List<AgeCheck> ageCheck();
	    /*按地址统计人数*/
	    @Select("select saddress as address,count(*) as count from studentmanagement.student group by saddress")
	    List<AddressCheck> addressCheck();
	    /*按政治面貌统计人数*/
	    @Select("select spol as spol,count(*) as count from studentmanagement.student group by spol")
	    List<SpolCheck> spolCheck();
	    /*查看全体学生成绩*/
	    @Select("select studentmanagement.score.sno,sname,smajor,sgrade,studentmanagement.score.cno,cname,score from studentmanagement.student,studentmanagement.score,studentmanagement.course where studentmanagement.student.sno=studentmanagement.score.sno and studentmanagement.course.cno=studentmanagement.score.cno")
	    List<AllScore> allScore();
	    /*查看各分数段的学生人数以及百分比*/
		@Select("select studentmanagement.course.cno as cno,cname as cname,sum(case when 0<=score and score<60 then 1 else 0 end) as count1,"
	    		+ "sum(case when 0<=score and score<60  then 1 else 0 end)/count(score) as percent1,"
	    		+ "sum(case when 60<=score and score<=69 then 1 else 0 end) as count2,"
	    		+ "sum(case when 60<=score and score<=69 then 1 else 0 end)/count(score) as percent2,"
	    		+ "sum(case when 70<=score and score<=79 then 1 else 0 end) as count3,"
	    		+ "sum(case when 70<=score and score<=79 then 1 else 0 end)/count(score) as percent3,"
	    		+ "sum(case when 80<=score and score<89 then 1 else 0 end) as count4,"
	    		+ "sum(case when 80<=score and score<=89 then 1 else 0 end)/count(score) as percent4,"
	    		+ "sum(case when 90<=score and score<=100 then 1 else 0 end) as count5,"
	    		+ "sum(case when 90<=score and score<=100 then 1 else 0 end)/count(score) as percent5 from studentmanagement.score,studentmanagement.student,studentmanagement.course where studentmanagement.student.sno=studentmanagement.score.sno and studentmanagement.course.cno=studentmanagement.score.cno and smajor=#{smajor} and sgrade=#{sgrade} group by studentmanagement.score.cno ")
	           List<Percent> getpercent(String smajor,Integer sgrade);
	    /*以专业分类，年级为单位查看学生成绩*/
	    @Select("select studentmanagement.student.sno,sname,smajor,sgrade,studentmanagement.course.cno,cname,score from studentmanagement.student,studentmanagement.course,studentmanagement.score where studentmanagement.student.sno=studentmanagement.score.sno and studentmanagement.course.cno=studentmanagement.score.cno and smajor=#{smajor} and sgrade=#{sgrade}")
        List<AllScore> checkScore(String smajor,Integer sgrade);
        /*修改成绩*/
	    @Update("update studentmanagement.score set score=#{score} where sno=#{sno}")
	    void updateScore(Double score,Integer sno);
	    @Select("select studentmanagement.score.sno as sno,sname as sname,smajor as smajor,sgrade as sgrade,studentmanagement.course.cno as cno,cname as cname,score as score from studentmanagement.score,studentmanagement.student,studentmanagement.course where studentmanagement.student.sno=studentmanagement.score.sno and studentmanagement.course.cno=studentmanagement.score.cno and studentmanagement.score.sno=#{sno}")
        List<AllScore> getScore(Integer sno);
}
